﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Business.Struct;
using System.Data.OracleClient;
using System.Configuration;
using WF_DataAccess;
using WF_Business;

namespace Business.FlowOperation
{
    /// <summary>
    /// 针对申报系统的流程发布设置类
    /// </summary>
    public class WorkFlowPubSetting
    {
        /// <summary>
        ///获取所有待发布的流程信息
        /// </summary>
        /// <param name="strUserID">用户ID</param>
        /// <returns></returns>
        /// <!--添加用户ID条件  editby zhongjian 20091125-->
        public DataTable GetPubWorkFlow(string strUserID)
        {
            DataTable dtReturn;
            string strSql = @"select rownum 序号,a.* from (select t.id,t.flowname,t.flowtype,t.flownum,
                    to_char(CREATETIME,'yyyy-mm-dd HH24:MI') CREATETIME,to_char(EDITTIME,'yyyy-mm-dd HH24:MI') EDITTIME,ispub,
                    to_char(PUBTIME,'yyyy-mm-dd HH24:MI') PUBTIME,(case when t.Ispub = 1 then '已发布' else '未发布' end) pubtext,
                    INTERFACEURL,INTERFACETYPE
              from xt_workflow_define t WHERE isdelete='0' ";
            if (!string.IsNullOrEmpty(strUserID))
            {
                strSql += string.Format(@" and flowname in (select wname  from st_workflow
                    where wid in (select gid from ST_USER_GROUP where userid = '{0}'))", strUserID);
            }
            strSql += string.Format(@" order by flownum) a");
            SysParams.OAConnection().RunSql(strSql, out dtReturn);

            return dtReturn;
        }

        /// <summary>
        /// 查询流程扩展表信息
        /// </summary>
        /// <param name="strId">ID</param>
        /// <param name="strFlowName">流程名称</param>
        /// <returns></returns>
        public DataTable GetWorkFlowView(string strId, string strFlowName)
        {
            DataTable dtReturn;
            string strSql = string.Format(@"select id,
                                                   flowname,
                                                   flowtype,
                                                   flownum,
                                                   ischeck,
                                                   createtime,
                                                   edittime,
                                                   pubtime,
                                                   ispub,
                                                   isdelete,
                                                   can_sync,
                                                   sync_type,
                                                   issave,
                                                   isshowdialog,
                                                   interfaceurl,
                                                   interfacetype
                                              from xt_workflow_define
                                             where ispub = '1'
                                                   and isdelete = '0'
                                                   and issave = '1'");
            if (!string.IsNullOrEmpty(strId))
                strSql += string.Format(" and id = '{0}'", strId);
            if (!string.IsNullOrEmpty(strFlowName))
                strSql += string.Format(" and flowname = '{0}'", strFlowName);

            SysParams.OAConnection().RunSql(strSql, out dtReturn);
            return dtReturn;
        }

        /// <summary>
        /// 获取其它连接信息
        /// </summary>
        /// <param name="strId">ID</param>
        /// <param name="strFlowID">流程ID</param>
        /// <returns></returns>
        /// <!--addby zhongjian 20100310-->
        public DataTable GetFlowLink(string strId, string strFlowID)
        {
            string strSql = string.Empty;
            DataTable dtReturn;
            strSql = string.Format(@"select rownum,id,FLOWID,TITLE,IMGSRC,LINKEHREF,createdate from xt_flow_link where 1=1 ");
            if (!string.IsNullOrEmpty(strId))
                strSql += string.Format(" and id='{0}'", strId);
            if (!string.IsNullOrEmpty(strFlowID))
                strSql += string.Format(" and flowid='{0}'", strFlowID);
            SysParams.OAConnection().RunSql(strSql, out dtReturn);
            return dtReturn;
        }

        /// <summary>
        /// 删除其它连接信息
        /// </summary>
        /// <param name="strId">id</param>
        /// <!--addby zhongjian 20100310-->
        public void DeleteFlowLink(string strId)
        {
            string strSql = string.Format("delete from xt_flow_link where id='{0}'", strId);

            SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// Gets all pub work flow.
        /// </summary>
        /// <param name="strId">The STR id.</param>
        /// <returns></returns>
        public DataTable GetAllPubWorkFlow(string strId)
        {
            string strSql = string.Format(@"select t.id,t.flowname,t.flowtype,t.flownum,ischeck,issave,ispub,
                    to_char(CREATETIME,'yyyy-mm-dd HH24:MI') CREATETIME,to_char(EDITTIME,'yyyy-mm-dd HH24:MI') EDITTIME,ispub,
                    to_char(PUBTIME,'yyyy-mm-dd HH24:MI') PUBTIME,(case when t.Ispub = 1 then '已发布' else '未发布' end) pubtext,
                    INTERFACEURL,INTERFACETYPE
                    from xt_workflow_define t WHERE id='{0}'", strId);
            DataTable dtReturn;
            SysParams.OAConnection().RunSql(strSql, out dtReturn);

            return dtReturn;
        }

        /// <summary>
        /// Deletes the pub work flow.
        /// </summary>
        /// <param name="strId">The STR id.</param>
        /// <returns></returns>
        public int DeletePubWorkFlow(string strId)
        {
            string strSql = string.Format(@"delete from XT_WORKFLOW_DEFINE t where t.id='{0}'",strId);
            
            return SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// 返回Id
        /// </summary>
        /// <param name="pwfParam">The PWF param.</param>
        /// <returns></returns>
        public string GetId(PubWorkFlowStruct pwfParam)
        {
            string strSql = string.Format("select id from XT_WORKFLOW_DEFINE t where t.FlowName='{0}'", pwfParam.FlowName);
            string strFlowId = SysParams.OAConnection().GetValue(strSql);
            return strFlowId;
        }

        /// <summary>
        /// Gets the work flow list.
        /// </summary>
        /// <param name="strFlowName">流程名</param>
        /// <returns></returns>
        public DataTable GetChildMenuRight(string strFlowName)
        {
            //添加已发布,和未删除的条件.edit by zhongjian 20091014
            string strSql = @"select a.id,
                       a.flowid,
                       a.menu_name,
                       a.menu_type,a.menu_appear,
                       b.flowname,
                       b.flowtype,
                       b.flownum
                  from xt_workflow_set a, xt_workflow_define b
                 where a.flowid = b.id
                   and b.ispub = '1'
                   and a.menu_appear = '1'
                   and b.isdelete = '0'";

            if (!string.IsNullOrEmpty(strFlowName))
            {
                strSql += string.Format(" and b.flowname='{0}' ", strFlowName);
            }
            strSql += " order by FLOWNUM asc";

            DataTable dtOut;
            SysParams.OAConnection().RunSql(strSql, out dtOut);
            return dtOut;
        }

       

        /// <summary>
        /// 根据流程名称获取WID
        /// </summary>
        /// <param name="strFlowName">流程名称</param>
        /// <returns></returns>
        /// <!--addby zhongjian 20091210-->
        public string GetWorkWid(string strFlowName)
        {
            string strSql = string.Format(@"select wid from st_workflow a where rot=0 and wname='{0}'", strFlowName);
            string strReturn = SysParams.OAConnection().GetValue(strSql);
            return strReturn;
        }

        /// <summary>
        /// 获取所有流程名称
        /// </summary>
        /// <param name="strParamsName">流程名称</param>
        /// <returns></returns>
        public DataTable GetAllFlows()
        {
            string strSql = "select WNAME keyvalue,wid keycode from st_workflow t where rot=0";
            DataTable dtOut;
            SysParams.OAConnection().RunSql(strSql, out dtOut);
            return dtOut;
        }

        /// <summary>
        /// 根据流程父ID获取菜单信息
        /// </summary>
        /// <param name="strParentId">父ID</param>
        /// <returns></returns>
        /// <!--addby zhongjian 20091010-->
        public DataTable GetSysMenuByParentid(string strParentId)
        {
            string strSql = string.Format(@"select t.* from sys_menu t where parent_id='{0}'  order by order_field", strParentId);
            DataTable dtReturn;
            SysParams.OAConnection().RunSql(strSql, out dtReturn);
            return dtReturn;
        }

        

        /// <summary>
        /// 按表名获取表中所有的字段名
        /// </summary>
        /// <param name="strTableName">表名</param>
        /// <returns>将字段名返回一张表</returns>
        /// <!--addby zhongjian 20091012-->
        public DataTable GetColumnName(string strTableName)
        {
            string strSql = string.Empty;
            strSql = string.Format(@"select column_name from user_tab_columns where table_name='{0}'", strTableName);
            DataTable dtReturn;
            SysParams.OAConnection().RunSql(strSql, out dtReturn);
            return dtReturn;
        }

        /// <summary>
        /// 获取某个流程没有发布的所有菜单设置内容
        /// </summary>
        /// <param name="strFlowID">流程ID</param>
        /// <returns></returns>
        /// <!--addby zhongjian 20091013-->
        public DataTable GetWorkFlowSet(string strFlowID)
        {
            string strSql = string.Empty;
            strSql = string.Format(@"select a.id,a.flowid,a.menu_name,a.menu_type,b.ispub,b.flowtype,b.flowname,b.flownum,a.MENU_APPEAR
                from XT_WORKFLOW_SET a,xt_workflow_define b where a.flowid=b.id  and a.flowid='{0}'", strFlowID);
            DataTable dtReturn;
            SysParams.OAConnection().RunSql(strSql, out dtReturn);
            return dtReturn;
        }

        /// <summary>
        /// 按流程ID获取流程设置信息
        /// </summary>
        /// <param name="strFlowID">流程ID</param>
        /// <returns></returns>
        ///  <!--addby zhongjian 20091013-->
        public DataTable GetWorkFlowList(string strFlowID)
        {
            string strSql = string.Empty;
            strSql = string.Format(@"select * from XT_WORKFLOW_SET where flowid='{0}'", strFlowID);
            DataTable dtReturn;
            SysParams.OAConnection().RunSql(strSql, out dtReturn);
            return dtReturn;
        }

        /// <summary>
        /// 按流程ID添加所有网上办事子菜单
        /// </summary>
        /// <param name="strFlowID">流程ID</param>
        /// <!--addby zhongjian 20091013-->
        public void SaveWorkFlowSet(string strFlowID)
        {
            string strParentID = ConfigurationManager.AppSettings["bsznId"];//流程父ID
            string strSql = string.Empty;
            string strMenuName = string.Empty;
            strSql = string.Format("delete from XT_WORKFLOW_SET where flowid='{0}'", strFlowID);
            SysParams.OAConnection().RunSql(strSql);

            DataTable dtTable = GetSysMenuByParentid(strParentID);

            if (dtTable.Rows.Count > 0)
            {
                //添加所有网上办事子菜单
                for (int i = 0; i < dtTable.Rows.Count; ++i)
                {
                    strMenuName = dtTable.Rows[i]["menu_title"].ToString();
                    strSql = string.Format("insert into XT_WORKFLOW_SET (flowID,menu_name) values ('{0}','{1}')", strFlowID, strMenuName);
                    SysParams.OAConnection().RunSql(strSql);
                }
            }
        }

        /// <summary>
        /// 保存网上办事权限的设置
        /// </summary>
        /// <param name="ltMenuType">设置列表</param>
        /// <param name="strFlowID">流程ID</param>
        /// <!--addby zhongjian 20091013-->
        public void SetWorkFlow(List<string> ltMenuType,string strFlowID)
        {
            string strSql = string.Empty;
            string strMenuName = string.Empty;
            if (ltMenuType.Count > 0)
            {
                for (int i = 0; i < ltMenuType.Count; ++i)
                {
                    strMenuName = ltMenuType[i].ToString();
                    strSql = string.Format("update XT_WORKFLOW_SET set menu_type=1 where menu_name='{0}' and flowID='{1}'", strMenuName, strFlowID);
                    SysParams.OAConnection().RunSql(strSql);
                }
            }
        }

        /// <summary>
        /// 保存网上办事权限的设置
        /// </summary>
        /// <param name="ltMenuType">子菜单匿名访问设置</param>
        /// <param name="lstMenuAppear">子菜单显示与否</param>
        /// <param name="strFlowID">流程ID</param>
        public void SetWorkFlow(List<string> ltMenuType, List<string> lstMenuAppear, string strFlowID)
        {
            string strSql = string.Empty;
            string strMenuName = string.Empty;

            for (int i = 0; i < ltMenuType.Count; ++i)
            {
                strMenuName = ltMenuType[i];
                strSql = string.Format("update XT_WORKFLOW_SET set menu_type=1 where menu_name='{0}' and flowID='{1}'", strMenuName, strFlowID);
                SysParams.OAConnection().RunSql(strSql);
            }

            for (int i = 0; i < lstMenuAppear.Count; ++i)
            {
                strMenuName = lstMenuAppear[i];
                strSql = string.Format("update XT_WORKFLOW_SET set menu_appear=1 where menu_name='{0}' and flowID='{1}'", strMenuName, strFlowID);
                SysParams.OAConnection().RunSql(strSql);
            }
        }

        /// <summary>
        /// 取出所有内容
        /// </summary>
        /// <returns></returns>
        public DataTable GetPubWorkFlow()
        {
            //添加已发布,和未删除的条件.edit by zhongjian 20091014
            string strSql = @"select * from XT_WORKFLOW_DEFINE where ispub='1' and isdelete='0' 
                order by FLOWNUM asc";
            DataTable dtReturn = new DataTable();
            SysParams.OAConnection().RunSql(strSql, out dtReturn);

            return dtReturn;
        }

        /// <summary>
        /// 设置流程发布
        /// </summary>
        /// <param name="strFlowID">流程ID</param>
        /// <param name="isPub">发布标识:(0:取消发布;1:发布)</param>
        /// <!--addby zhongjian 20091015-->
        public void PubWorkFlow(string strFlowID, string isPub)
        {
            string strSql = string.Empty;
            strSql = string.Format("update XT_WORKFLOW_DEFINE set ispub={1},edittime=sysdate,issave='0',pubtime=sysdate where id='{0}'", strFlowID, isPub);
            SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// 修改流程信息
        /// </summary>
        /// <param name="strFlowID">流程ID</param>
        /// <param name="strFlowName">流程名称</param>
        /// <param name="strFlowType">流程类型</param>
        /// <param name="strFlowNum">流程排序</param>
        /// <param name="strIsPub">发布标识</param>
        /// <param name="strIsDelete">删除标识</param>
        /// <param name="strCheck">上传材料是否校验</param>
        /// <param name="Interfacetype">接口地址启用类型(0:不启用;1:启用)</param>
        /// <param name="strInterfaceUrl">接口地址</param>
        /// <!--addby zhongjian 20091014-->
        public void UpdateWorkFlow(string strFlowID,string strFlowName,string strFlowType,string strFlowNum,string strIsPub,string strIsDelete,
            int strCheck, int Interfacetype, string strInterfaceUrl)
        {
            string strSql = string.Empty;
            string strMenuName = string.Empty;
            strSql = string.Format(" update xt_workflow_define set edittime=sysdate,ischeck='{0}',INTERFACETYPE='{1}',INTERFACEURL='{2}' ",
                strCheck, Interfacetype, strInterfaceUrl);
            if (strFlowName != "")
            {
                strSql += string.Format(",flowname='{0}' ",strFlowName);
            }
            if (strFlowType != "")
            {
                strSql += string.Format(",flowtype='{0}' ", strFlowType);
            }
            if (strFlowNum != "")
            {
                strSql += string.Format(",flownum='{0}' ", strFlowNum);
            }
            if(strIsPub=="1")
            {
                strSql+=",pubtime=sysdate ";
            }
            if (strIsDelete != "")
            {
                strSql += ",isdelete=1";
            }
            strSql+=string.Format("where id='{0}'", strFlowID);
            SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// 保存或发布程序方法
        /// </summary>
        /// <param name="FlowName">流程名称</param>
        /// <param name="FlowType">流程类型</param>
        /// <param name="FlowNum">流程排序</param>
        /// <param name="ltMenuType">子菜单权限</param>
        /// <param name="strPub">是否发布(0:不发布;1:发布)</param>
        /// <param name="strCheck">上传材料是否校验(0:不校验;1:校验)</param>
        /// <param name="Interfacetype">接口地址启用类型(0:不启用;1:启用)</param>
        /// <param name="strInterfaceUrl">接口地址</param>
        /// <!--addby zhongjian 20091015-->
        public void InsertWorkFlow(string FlowName, string FlowType, string FlowNum, List<string> ltMenuType,string strPub,
            int strCheck,int Interfacetype,string strInterfaceUrl)
        {
            string strSql = string.Empty;
            string strFlowID = string.Empty;//流程ID
            strSql = string.Format(@"insert into XT_WORKFLOW_DEFINE t(FLOWNAME,flowtype, flownum,ISCHECK,INTERFACETYPE,INTERFACEURL) 
                values('{0}','{1}','{2}','{3}','{4}','{5}') returning id into :newid",
                FlowName, FlowType, FlowNum, strCheck, Interfacetype, strInterfaceUrl);
            //分两步:先新增流程,再新增流程权限
            IDataParameter[] idaArr = new OracleParameter[1];
            idaArr[0] = new OracleParameter(":newId", OracleType.VarChar, 32);
            idaArr[0].Direction = ParameterDirection.Output;
            SysParams.OAConnection().RunSql(strSql, ref idaArr);
            strFlowID = idaArr[0].Value.ToString();

            if (!string.IsNullOrEmpty(strFlowID))
            {
                SaveWorkFlowSet(strFlowID);//保存子菜单名称
                SetWorkFlow(ltMenuType, strFlowID);//保存子菜单权限
                if (strPub == "1")
                {
                    PubWorkFlow(strFlowID, strPub);//发布流程
                }
            }
        }

        /// <summary>
        /// 保存或发布程序方法
        /// </summary>
        /// <param name="FlowName">流程名称</param>
        /// <param name="FlowType">流程类型</param>
        /// <param name="FlowNum">流程排序</param>
        /// <param name="ltMenuType">子菜单是否匿名的权限</param>
        /// <param name="strPub">是否发布(0:不发布;1:发布)</param>
        /// <param name="strCheck">上传材料是否校验(0:不校验;1:校验)</param>
        /// <param name="Interfacetype">接口地址启用类型(0:不启用;1:启用)</param>
        /// <param name="strInterfaceUrl">接口地址</param>
        /// <param name="lstMenuAppear">子菜单显示与否</param>
        public void InsertWorkFlow(string FlowName, string FlowType, string FlowNum, List<string> ltMenuType, string strPub, int strCheck, int Interfacetype, string strInterfaceUrl, List<string> lstMenuAppear)
        {
            string strSql = string.Empty;
            string strFlowID = string.Empty;//流程ID
            strSql = string.Format(@"insert into XT_WORKFLOW_DEFINE t(FLOWNAME,flowtype, flownum,ISCHECK,INTERFACETYPE,INTERFACEURL) 
                values('{0}','{1}','{2}','{3}','{4}','{5}') returning id into :newid",
                FlowName, FlowType, FlowNum, strCheck, Interfacetype, strInterfaceUrl);
            //分两步:先新增流程,再新增流程权限
            IDataParameter[] idaArr = new OracleParameter[1];
            idaArr[0] = new OracleParameter(":newId", OracleType.VarChar, 32);
            idaArr[0].Direction = ParameterDirection.Output;
            SysParams.OAConnection().RunSql(strSql, ref idaArr);
            strFlowID = idaArr[0].Value.ToString();

            if (!string.IsNullOrEmpty(strFlowID))
            {
                SaveWorkFlowSet(strFlowID);//保存子菜单名称
                SetWorkFlow(ltMenuType, lstMenuAppear, strFlowID);//保存子菜单权限
                if (strPub == "1")
                {
                    PubWorkFlow(strFlowID, strPub);//发布流程
                }
            }
        }

        /// <summary>
        /// 检查流程名称不能重复
        /// </summary>
        /// <param name="FlowName">流程名称</param>
        /// <param name="FlowType">流程类型</param>
        /// <param name="strPub">发布标识</param>
        /// <returns></returns>
        /// <!--addby zhongjian 20091015-->
        public bool CheckFlowName(string FlowName, string FlowType, string strPub)
        {
            bool strReturn = false;
            string strSql = string.Empty;
            string strFlowName = string.Empty;
            string strFlowType = string.Empty;
            DataTable dtReturn;
            strSql = string.Format(@"select t.id,t.flowname,t.flowtype,t.ispub from xt_workflow_define t
                                     WHERE isdelete = '0' and ispub='{0}' and flowname='{1}' ", strPub, FlowName);
            if (!string.IsNullOrEmpty(FlowType))
            {
                strSql+=string.Format("and flowtype='{0}'",FlowType);
            }
            SysParams.OAConnection().RunSql(strSql, out dtReturn);
            if (dtReturn.Rows.Count > 0)
            {
                strReturn = true;
            }
            return strReturn;
        }
        /// <summary>
        /// 按条件获取已经存在的流程ID
        /// </summary>
        /// <param name="FlowName">流程名称</param>
        /// <param name="FlowType">流程类型</param>
        /// <param name="strPub">发布标识</param>
        /// <param name="strdelete">删除标识</param>
        /// <returns>流程ID</returns>
        /// <!--add by zhongjian 20091016-->
        public string GetWorkFlowId(string FlowName, string FlowType, string strPub, string strdelete)
        {
            string strSql = string.Format(@"select id from XT_WORKFLOW_DEFINE t 
                                            WHERE isdelete = '{0}' and ispub='{1}' and flowname='{2}' ", strdelete,strPub, FlowName);
            if (!string.IsNullOrEmpty(FlowType))
            {
                strSql += string.Format("and flowtype='{0}'", FlowType);
            }
            string strFlowId = SysParams.OAConnection().GetValue(strSql);
            return strFlowId;
        }

        /// <summary>
        /// 流程数据全部提交
        /// </summary>
        /// <param name="FlowID">流程ID</param>
        /// <param name="FlowName">流程名称</param>
        /// <!--addby zhongjian 20091026-->
        public void SetCanSync(string FlowID,string FlowName)
        {
            string strSql = string.Empty;
            IDataAccess dataAccess= SysParams.OAConnection(true);
            try
            {
                strSql = string.Format(@"update xt_workflow_define set can_sync='1',issave='0' where id='{0}'", FlowID);
                dataAccess.RunSql(strSql);
                strSql = string.Format(@"update xt_workflow_set set can_sync='1' where flowid='{0}'", FlowID);
                dataAccess.RunSql(strSql);
                strSql = string.Format(@"update SYS_RESOURCE set can_sync='1' where flowid='{0}'", FlowID);
                dataAccess.RunSql(strSql);
                strSql = string.Format(@"update XT_PROCESS set can_sync='1' where flowid='{0}'", FlowID);
                dataAccess.RunSql(strSql);
                strSql = string.Format(@"update XT_SERIAL_MODULE set can_sync='1' where flowid='{0}'", FlowID);
                dataAccess.RunSql(strSql);
                strSql = string.Format(@"update xt_other set can_sync='1' where title='{0}'", FlowName);
                dataAccess.RunSql(strSql);

                dataAccess.Close(true);
            }
            catch
            {
                dataAccess.Close(false);
                throw;
            }
        }

        /// <summary>
        /// 获取流程排序最大值
        /// </summary>
        /// <returns></returns>
        /// <!--addby zhongjian 20091027-->
        public string GetDefineMax()
        {
            string strSql = string.Format(@"select max(flownum) from xt_workflow_define where isdelete<>1");
            string strFlowNum = SysParams.OAConnection().GetValue(strSql);
            return strFlowNum;
        }

        /// <summary>
        /// 设置流程图办理人员是否显示
        /// </summary>
        /// <param name="strID">流程ID</param>
        /// <param name="isShow">显示类型:(0:不显示;1:显示)</param>
        public void SetShowDialog(string strID, string isShow)
        {
            string strSql = string.Empty;
            strSql = string.Format("update xt_workflow_define set IsShowDialog='{1}' where id = '{0}'",
                strID, isShow);
            SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// 根据流程名称获取流程图办理人员 显示类型
        /// </summary>
        /// <param name="strFlowName">流程名称</param>
        /// <returns></returns>
        /// <!--addby zhongjian 20091210-->
        public bool GetIsShowDialog(string strFlowName)
        {
            string strSql = string.Format(@"select IsShowDialog  from xt_workflow_define
                            where ispub = 1 and isdelete = 0 and flowname = '{0}'", strFlowName);
            string strFlowNum = SysParams.OAConnection().GetValue(strSql);
            if (strFlowNum == "1")
                return true;
            return false;
        }

        /// <summary>
        /// 按条件获取业务信息
        /// </summary>
        /// <param name="striid">业务编号</param>
        /// <param name="strwname">业务类型</param>
        /// <param name="strname">申请单位</param>
        /// <param name="strdatebegin">接件时间(开始)</param>
        /// <param name="strdateend">接件时间(结束)</param>
        /// <param name="strStatus">案件状态</param>
        /// <returns></returns>
        /// <!--addby zhongjian 20091222-->
        public DataTable GetInstance(string striid,string strwname,string strname,string strdatebegin,string strdateend,string strStatus)
        {
            string strSql = string.Empty;
            strSql = string.Format(@"select i.iid,
                                           w.wname 业务类型,
                                           i.name 申请单位,
                                           (select 联系人 from xt_instance_ext where iid = i.iid) 联系人,
                                           i.EXBEGINMONITOR 接件时间,
                                           i.priority 优先级,
                                           
                                           (case
                                             when i.status = '1' then
                                              '正在办理'
                                             when i.status = '2' then
                                              '已通过'
                                             when i.status = '-2' then
                                              '未通过'
                                             else
                                              '未知'
                                           end) as 案件状态
                                      from st_instance i, st_workflow w
                                     where w.wid = i.wid ");
            if (!string.IsNullOrEmpty(striid))
                strSql += string.Format("and i.iid ='{0}'", striid);
            if (!string.IsNullOrEmpty(strwname))
                strSql += string.Format("and w.wname like '%{0}%'", strwname);
            if (!string.IsNullOrEmpty(strname))
                strSql += string.Format("and i.name like '%{0}%'",strname);
            if (!string.IsNullOrEmpty(strdatebegin))
                strSql += string.Format("and i.EXBEGINMONITOR>to_date('{0}','yyyy-mm-dd')", strdatebegin);
            if (!string.IsNullOrEmpty(strdateend))
                strSql += string.Format("and i.EXBEGINMONITOR<to_date('{0}','yyyy-mm-dd')", strdateend);
            if (!string.IsNullOrEmpty(strStatus))
                strSql += string.Format("and status='{0}' and isdelete <>1", strStatus);
            else
                strSql += string.Format("and isdelete = 1");
            strSql += string.Format(" order by i.iid desc");
            DataTable dtReturn;
            SysParams.OAConnection().RunSql(strSql, out dtReturn);
            return dtReturn;
        }

        /// <summary>
        /// 获取办事指南信息
        /// </summary>
        /// <param name="strFlowID">流程ID</param>
        /// <returns></returns>
        /// <!--addby zhongjian 20100118-->
        public DataTable GetLawGuide(string strFlowID)
        {
            string strSql = string.Empty;
            strSql = string.Format(@"select * from xt_lawguide where flowid='{0}' order by forder", strFlowID);
            DataTable dtReturn;
            SysParams.OAConnection().RunSql(strSql, out dtReturn);
            return dtReturn;
        }

        /// <summary>
        /// 保存办事指南
        /// </summary>
        /// <param name="ID">ID</param>
        /// <param name="strFileName">流程名称</param>
        /// <param name="strFlowid">流程ID</param>
        /// <param name="strTitle">标题</param>
        /// <param name="strContent">内容</param>
        /// <param name="strOrder">排序</param>
        /// <!--addby zhongjian 20100118-->
        public void SaveLawGuide(string strFileName, string strFlowid,string strTitle,string strContent,string strOrder)
        {
            string strSql = string.Empty;
            strSql = string.Format("insert into xt_lawguide(filename,flowid,ftitle,fcontent,forder) values('{0}','{1}','{2}','{3}','{4}')",
                strFileName, strFlowid, strTitle, strContent, strOrder);
            SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// 删除办事指南
        /// </summary>
        /// <param name="strFlowid">流程ID</param>
        /// <!--addby zhongjian 20100118-->
        public void DeleteLawGuide(string strFlowid)
        {
            string strSql = string.Empty;
            strSql = string.Format("delete from xt_lawguide where flowid='{0}'", strFlowid);
            SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// 添加其它连接信息
        /// </summary>
        /// <param name="strFlowID">流程id</param>
        /// <param name="strTitle">链接标题</param>
        /// <param name="strImgScr">对应图片地址</param>
        /// <param name="strLinkHref">链接地址</param>
        /// <!--addby zhongjian 20100310-->
        public void InsertFlowLink(string strFlowID, string strTitle, string strImgScr, string strLinkHref)
        {
            if (!string.IsNullOrEmpty(strFlowID))
            {
                string strSql = string.Format(@"Insert into xt_flow_link (FLOWID,TITLE,IMGSRC,LINKEHREF) VALUES ('{0}','{1}','{2}','{3}')",
                    strFlowID, strTitle, strImgScr, strLinkHref);

                SysParams.OAConnection().RunSql(strSql);
            }
        }

        /// <summary>
        /// 添加其它连接信息
        /// </summary>
        /// <param name="strFlowID">流程id</param>
        /// <param name="strTitle">链接标题</param>
        /// <param name="strImgScr">对应图片地址</param>
        /// <param name="strLinkHref">链接地址</param>
        /// <!--addby zhongjian 20100310-->
        public void UpdateFlowLink(string strID, string strFlowID, string strTitle, string strImgScr, string strLinkHref)
        {
            if (!string.IsNullOrEmpty(strFlowID) && !string.IsNullOrEmpty(strID))
            {
                string strSql = string.Format(@"Update xt_flow_link set FLOWID='{1}',TITLE='{2}',IMGSRC='{3}',LINKEHREF='{4}' 
                                    where id='{0}'", strID, strFlowID, strTitle, strImgScr, strLinkHref);

                SysParams.OAConnection().RunSql(strSql);
            }
        }
    }
}
